﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace ExecSql
{
    class Program
    {
        /// <summary>
        /// 接收外部参数,执行指定目录的SQL,并指定需要执行的库(ConnectionString的Key需要在Appconfig中提前配置好)
        /// </summary>
        /// <param name="args">"e:/sqlfile/exec/proc" "db1,db2"</param>
        static void Main(string[] args)
        {
            Console.WriteLine($"SqlExec开始运行.....");

            //获取第一个参数（存放sql文件的目录）:eg("e:/sqlfile/exec/proc")
            var sqlpath = args[0];

            //第二个参数(指定调用的连接字符串,支持多个库用小写逗号隔开) eg("db1,db2")
            var connKeys = args[1].Split(',');

            Console.WriteLine($"参数指定SQL脚本目录:【{sqlpath}】  DB:【{args[1]}】");


            //获取指定文件夹下的所有sql文件,并按升序排列（如文件有执行顺序，确保顺序正确）
            DirectoryInfo root = new DirectoryInfo(sqlpath);
            var files = root.GetFiles().Where(s=>s.Extension.Equals(".sql")).ToList().OrderBy(s=>s.Name).ToList();
            Console.WriteLine($"读取目录完成,共有{files.Count}个脚本");

            Console.WriteLine("执行顺序如下:");
            foreach (var f in files)
            {
                Console.WriteLine(f.Name);
            }

            BatchExecSqlFile(files, connKeys);
        }

        /// <summary>
        /// 将所有的SQL文件合并,确保执行顺序
        /// </summary>
        public static void BatchExecSqlFile(List<FileInfo> files,string[] connKeys)
        {
            //将所有SQL文件拼接为一整个sql文件，一次性执行
            StringBuilder sqlStrBuilder = new StringBuilder();
            foreach (var f in files)
            {

                var sqlinfo = System.IO.File.ReadAllText(f.FullName, encoding: Encoding.UTF8);

                Console.WriteLine($"脚本:【{f.Name}】读取完毕,内容如下:");
                sqlinfo.ConsoleWriteLinColer(ConsoleColor.Blue);

                sqlStrBuilder.Append(sqlinfo);
                //多文件合并后，在末尾添加GO

                sqlStrBuilder.Append(@"
GO
");
            }

            var execStr = sqlStrBuilder.ToString();

            Console.WriteLine($"合并脚本完成,即将执行:");
            execStr.ConsoleWriteLinColer(ConsoleColor.Yellow);

            //在指定的多个数据库中分别执行SQL脚本
            foreach (var key in connKeys)
            {
                ExecuteSql(execStr, key);
            }
        }

        /// <summary>
        /// 执行带GO的SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connKey"></param>
        public static void ExecuteSql(String sql,string connKey)
        {
            string connectionString = ConfigurationManager.ConnectionStrings[connKey].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //注： 此处以 换行_后面带0到多个空格_再后面是go 来分割字符串
                    String[] sqlArr = Regex.Split(sql.Trim(), "\r\n\\s*go", RegexOptions.IgnoreCase);
                    foreach (string strsql in sqlArr)
                    {
                        if (strsql.Trim().Length > 1 && strsql.Trim() != "\r\n")
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();

                            strsql.ConsoleWriteLinColer(ConsoleColor.Green);
                            "执行成功！".ConsoleWriteLinColer(ConsoleColor.Green);
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    "执行失败！".ConsoleWriteLinColer(ConsoleColor.Red);
                    cmd.CommandText.ConsoleWriteLinColer(ConsoleColor.Red);
                    "错误信息:".ConsoleWriteLinColer(ConsoleColor.Red);
                    E.Message.ConsoleWriteLinColer(ConsoleColor.Red);
                    throw new Exception(E.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        
    }
}
